Mecklenburg County Sheriff's Office -- Contraband Data Investigation¶

Date: July 14, 2022

In [1]:
import logging


def configure_root_logger():
    console_logger = logging.StreamHandler()
    console_logger.setLevel(logging.DEBUG)
    console_logger.setFormatter(
        logging.Formatter("%(asctime)s - %(levelname)s - %(message)s")
    )

    logger = logging.getLogger()
    logger.handlers = []  # reset handlers in case this cell is re-run
    logger.setLevel(logging.DEBUG)
    logger.addHandler(console_logger)
    return logger
In [2]:
import os

from sqlalchemy import create_engine

from dash import Dash, html, dcc
import plotly.express as px
import pandas as pd
In [3]:
import plotly
plotly.offline.init_notebook_mode()
In [4]:
logger = configure_root_logger()
pg_engine = create_engine("postgresql://postgres@127.0.0.1:54344/traffic_stops_nc")
pg_conn = pg_engine.connect()

Total Traffic Stops¶

In [5]:
df = pd.read_sql(
    f"""
    SELECT
        id
        , name
    FROM nc_agency
    WHERE name ~ 'Mecklenburg County'
    """,
    pg_conn,
)
agency_id = df.iloc[0]['id']
df
Out[5]:
id name
0 168 Mecklenburg County Sheriff's Office
In [6]:
df = pd.read_sql(
    f"""
    SELECT
        MIN(date)::date AS first_reported_stop
        , MAX(date)::date AS last_reported_stop
        , COUNT(*) AS total_stops
    FROM nc_stop
    WHERE nc_stop.agency_id = {agency_id}
    """,
    pg_conn,
)
df.T
Out[6]:
0
first_reported_stop 2002-01-01
last_reported_stop 2022-07-13
total_stops 41952

The Mecklenburg County Sheriff's Office made about 40k stops in the past ~20 years.

Total Searches and Seizures¶

In [7]:
mecklenburg_driver_stops = f"""
SELECT
    "nc_stop"."stop_id"
    , "nc_stop"."date"
    , DATE_TRUNC('year', date AT TIME ZONE 'America/New_York') AS "year"
    , (CASE WHEN nc_person.ethnicity = 'H' THEN 'Hispanic'
            WHEN nc_person.ethnicity = 'N' AND nc_person.race = 'A' THEN 'Asian'
            WHEN nc_person.ethnicity = 'N' AND nc_person.race = 'B' THEN 'Black'
            WHEN nc_person.ethnicity = 'N' AND nc_person.race = 'I' THEN 'Native American'
            WHEN nc_person.ethnicity = 'N' AND nc_person.race = 'U' THEN 'Other'
            WHEN nc_person.ethnicity = 'N' AND nc_person.race = 'W' THEN 'White'
       END) as driver_race
    , "nc_stop"."officer_id"
    , (CASE WHEN nc_stop.purpose = 1  THEN 'Speed Limit Violation'         -- "Safety stop"
            WHEN nc_stop.purpose = 2  THEN 'Stop Light/Sign Violation'     -- "Safety stop"
            WHEN nc_stop.purpose = 3  THEN 'Driving While Impaired'        -- "Safety stop"
            WHEN nc_stop.purpose = 4  THEN 'Safe Movement Violation'       -- "Safety stop"
            WHEN nc_stop.purpose = 8  THEN 'Investigation'                 -- "Investigatory stop"
            WHEN nc_stop.purpose = 7  THEN 'Seat Belt Violation'           -- "Investigatory stop"
            WHEN nc_stop.purpose = 10 THEN 'Checkpoint'                    -- "Investigatory stop"
            WHEN nc_stop.purpose = 5  THEN 'Vehicle Equipment Violation'   -- "Economic stop"
            WHEN nc_stop.purpose = 6  THEN 'Vehicle Regulatory Violation'  -- "Economic stop"
            WHEN nc_stop.purpose = 9  THEN 'Other Motor Vehicle Violation' -- "Economic stop"
       END) as stop_purpose
    , (CASE WHEN nc_stop.purpose IN (1, 2, 3, 4) THEN 'Safety Violation'
            WHEN nc_stop.purpose IN (7, 8, 10) THEN 'Investigatory'
            WHEN nc_stop.purpose IN (5, 6, 9) THEN 'Regulatory and Equipment'
            ELSE 'Other'
       END) as stop_purpose_group
    , (nc_search.search_id IS NOT NULL) AS driver_searched
    , "nc_search"."search_id"
    , (CASE WHEN nc_search.type = 1 THEN 'Consent'
            WHEN nc_search.type = 2 THEN 'Search Warrant'
            WHEN nc_search.type = 3 THEN 'Probable Cause'
            WHEN nc_search.type = 4 THEN 'Search Incident to Arrest'
            WHEN nc_search.type = 5 THEN 'Protective Frisk'
            ELSE ''
       END) as search_type
    , (CASE
        WHEN nc_contraband.contraband_id IS NULL THEN false
        ELSE true
        END) AS contraband_found
    , nc_contraband.contraband_id
    , (CASE WHEN nc_contraband.ounces > 0 OR nc_contraband.pounds > 0 OR nc_contraband.dosages > 0 OR nc_contraband.grams > 0 OR nc_contraband.kilos > 0 THEN true
            ELSE false
       END) AS drugs_found
    , (CASE WHEN nc_contraband.pints > 0 OR nc_contraband.gallons > 0 THEN true
            ELSE false
       END) AS alcohol_found
    , (CASE WHEN nc_contraband.weapons > 0 THEN true
            ELSE false
       END) AS weapons_found
    , (CASE WHEN nc_contraband.money > 0 THEN true
            ELSE false
       END) AS money_found
    , (CASE WHEN nc_contraband.dollar_amount > 0 THEN true
            ELSE false
       END) AS other_found
    , nc_contraband.ounces AS contraband_ounces
    , nc_contraband.pounds AS contraband_pounds
    , nc_contraband.dosages AS contraband_dosages
    , nc_contraband.grams AS contraband_grams
    , nc_contraband.kilos AS contraband_kilos
    , nc_contraband.pints AS contraband_pints
    , nc_contraband.gallons AS contraband_gallons
    , nc_contraband.weapons AS contraband_weapons
    , nc_contraband.money AS contraband_money
    , nc_contraband.dollar_amount AS contraband_dollar_amount
FROM "nc_stop"
INNER JOIN "nc_person"
    ON ("nc_stop"."stop_id" = "nc_person"."stop_id" AND "nc_person"."type" = 'D')
LEFT OUTER JOIN "nc_search"
    ON ("nc_stop"."stop_id" = "nc_search"."stop_id")
LEFT OUTER JOIN "nc_contraband"
    ON ("nc_stop"."stop_id" = "nc_contraband"."stop_id")
WHERE
    nc_stop.agency_id = {agency_id}
ORDER BY nc_stop.date ASC
"""
In [8]:
df = pd.read_sql(
    f"""
    WITH stops AS ({mecklenburg_driver_stops})
    SELECT
        driver_searched
        , COUNT(*) AS stop_count
    FROM stops
    GROUP BY 1
    ORDER BY 1
    """,
    pg_conn,
)
df
Out[8]:
driver_searched stop_count
0 False 41597
1 True 355

Drivers were searched in 355 of the ~42k total stops.

In [9]:
df = pd.read_sql(
    f"""
    WITH stops AS ({mecklenburg_driver_stops})
    SELECT
        contraband_found
        , COUNT(*) AS stop_count
    FROM stops
    GROUP BY 1
    ORDER BY 1
    """,
    pg_conn,
)
df
Out[9]:
contraband_found stop_count
0 False 41845
1 True 107

Contraband was found in 107 of the ~42k total stops.

Contraband Seized¶

In [10]:
df = pd.read_sql(
    f"""
    WITH stops AS ({mecklenburg_driver_stops})
    SELECT
        drugs_found
        , alcohol_found
        , weapons_found
        , money_found
        , other_found
        , COUNT(*)
    FROM stops
    WHERE contraband_found = true
    GROUP BY 1, 2, 3, 4, 5
    ORDER BY 1, 2, 3, 4, 5
    """,
    pg_conn,
)
pd.concat([df, pd.DataFrame([{"count": df["count"].sum()}])], ignore_index=True)
Out[10]:
drugs_found alcohol_found weapons_found money_found other_found count
0 False False False False False 26
1 False False False False True 5
2 False False False True False 2
3 False False True False False 11
4 False False True True False 1
5 False True False False False 14
6 True False False False False 20
7 True False False True False 1
8 True False True False False 1
9 True False True True False 1
10 True True False False False 20
11 True True True False False 2
12 True True True True False 3
13 NaN NaN NaN NaN NaN 107

Multiple contraband seizures may occur during the same stop (e.g. both drugs and alcohol found).

In [11]:
df = pd.read_sql(
    f"""
    WITH stops AS ({mecklenburg_driver_stops})
    SELECT
        stop_purpose_group
        , driver_race
        , COUNT(*) FILTER (WHERE drugs_found = true) AS drugs_found
        , COUNT(*) FILTER (WHERE alcohol_found = true) AS alcohol_found
        , COUNT(*) FILTER (WHERE weapons_found = true) AS weapons_found
        , COUNT(*) FILTER (WHERE money_found = true) AS money_found
        , COUNT(*) FILTER (WHERE other_found = true) AS other_found
        , COUNT(*) FILTER (WHERE driver_searched = true) AS drivers_searched
        , COUNT(*) AS total_stops
    FROM stops
    GROUP BY 1, 2
    ORDER BY 1, 2
    """,
    pg_conn,
)
pd.concat([df, df[["drugs_found", "alcohol_found", "weapons_found", "money_found", "other_found", "drivers_searched", "total_stops"]].sum().to_frame().T], ignore_index=True)
Out[11]:
stop_purpose_group driver_race drugs_found alcohol_found weapons_found money_found other_found drivers_searched total_stops
0 Investigatory Asian 0 0 0 0 0 0 8
1 Investigatory Black 6 3 1 1 1 32 525
2 Investigatory Hispanic 0 0 0 0 0 2 143
3 Investigatory Native American 0 0 0 0 0 0 6
4 Investigatory Other 0 0 0 0 0 2 27
5 Investigatory White 4 2 0 0 0 12 327
6 Regulatory and Equipment Asian 0 0 0 0 0 0 96
7 Regulatory and Equipment Black 3 1 4 4 1 52 3890
8 Regulatory and Equipment Hispanic 1 0 0 0 0 15 732
9 Regulatory and Equipment Native American 0 0 0 0 0 2 57
10 Regulatory and Equipment Other 0 0 0 0 0 0 150
11 Regulatory and Equipment White 3 1 1 1 2 19 2215
12 Safety Violation Asian 0 0 0 0 0 1 753
13 Safety Violation Black 20 18 8 1 1 110 15528
14 Safety Violation Hispanic 4 5 3 1 0 41 4179
15 Safety Violation Native American 0 0 0 0 0 1 200
16 Safety Violation Other 0 0 0 0 0 4 996
17 Safety Violation White 7 9 2 0 0 62 12120
18 NaN NaN 48 39 19 8 5 355 41952

Breakdown of contraband seizures by race and stop purpose grouping.

In [12]:
df = pd.read_sql(
    f"""
    WITH stops AS ({mecklenburg_driver_stops})
    SELECT
        year
        , stop_purpose_group
        , driver_race
        , COUNT(*) FILTER (WHERE drugs_found = true) AS drugs_found
        , COUNT(*) FILTER (WHERE alcohol_found = true) AS alcohol_found
        , COUNT(*) FILTER (WHERE weapons_found = true) AS weapons_found
        , COUNT(*) FILTER (WHERE money_found = true) AS money_found
        , COUNT(*) FILTER (WHERE other_found = true) AS other_found
        , COUNT(*) FILTER (WHERE driver_searched = true) AS drivers_searched
        , COUNT(*) AS total_stops
    FROM stops
    WHERE year >= '2020-01-01'
    GROUP BY 1, 2, 3
    ORDER BY 1, 2, 3
    """,
    pg_conn,
)
pd.concat([df, df[["drugs_found", "alcohol_found", "weapons_found", "money_found", "other_found", "drivers_searched", "total_stops"]].sum().to_frame().T], ignore_index=True)
Out[12]:
year stop_purpose_group driver_race drugs_found alcohol_found weapons_found money_found other_found drivers_searched total_stops
0 2020-01-01 Investigatory Black 1 1 0 0 0 2 48
1 2020-01-01 Investigatory Hispanic 0 0 0 0 0 0 10
2 2020-01-01 Investigatory Native American 0 0 0 0 0 0 1
3 2020-01-01 Investigatory Other 0 0 0 0 0 0 2
4 2020-01-01 Investigatory White 0 0 0 0 0 0 24
5 2020-01-01 Regulatory and Equipment Asian 0 0 0 0 0 0 3
6 2020-01-01 Regulatory and Equipment Black 0 0 0 0 0 0 340
7 2020-01-01 Regulatory and Equipment Hispanic 0 0 0 0 0 0 50
8 2020-01-01 Regulatory and Equipment Native American 0 0 0 0 0 0 4
9 2020-01-01 Regulatory and Equipment Other 0 0 0 0 0 0 6
10 2020-01-01 Regulatory and Equipment White 0 0 0 0 0 0 146
11 2020-01-01 Safety Violation Asian 0 0 0 0 0 0 102
12 2020-01-01 Safety Violation Black 2 1 1 0 0 4 2633
13 2020-01-01 Safety Violation Hispanic 0 0 0 0 0 2 692
14 2020-01-01 Safety Violation Native American 0 0 0 0 0 0 37
15 2020-01-01 Safety Violation Other 0 0 0 0 0 0 147
16 2020-01-01 Safety Violation White 3 1 0 0 0 3 1995
17 2021-01-01 Investigatory Black 0 0 0 0 0 1 82
18 2021-01-01 Investigatory Hispanic 0 0 0 0 0 0 38
19 2021-01-01 Investigatory Other 0 0 0 0 0 0 6
20 2021-01-01 Investigatory White 1 1 0 0 0 1 32
21 2021-01-01 Regulatory and Equipment Asian 0 0 0 0 0 0 6
22 2021-01-01 Regulatory and Equipment Black 0 0 0 0 0 0 440
23 2021-01-01 Regulatory and Equipment Hispanic 1 0 0 0 0 1 120
24 2021-01-01 Regulatory and Equipment Native American 0 0 0 0 0 0 4
25 2021-01-01 Regulatory and Equipment Other 0 0 0 0 0 0 22
26 2021-01-01 Regulatory and Equipment White 0 0 0 0 0 0 247
27 2021-01-01 Safety Violation Asian 0 0 0 0 0 0 144
28 2021-01-01 Safety Violation Black 5 2 1 0 0 10 3457
29 2021-01-01 Safety Violation Hispanic 0 0 1 0 0 2 1089
30 2021-01-01 Safety Violation Native American 0 0 0 0 0 0 33
31 2021-01-01 Safety Violation Other 0 0 0 0 0 0 236
32 2021-01-01 Safety Violation White 1 0 1 0 0 2 2328
33 2022-01-01 Investigatory Asian 0 0 0 0 0 0 2
34 2022-01-01 Investigatory Black 0 0 0 0 0 1 38
35 2022-01-01 Investigatory Hispanic 0 0 0 0 0 0 8
36 2022-01-01 Investigatory Other 0 0 0 0 0 0 2
37 2022-01-01 Investigatory White 0 0 0 0 0 0 7
38 2022-01-01 Regulatory and Equipment Asian 0 0 0 0 0 0 5
39 2022-01-01 Regulatory and Equipment Black 0 0 0 0 0 1 220
40 2022-01-01 Regulatory and Equipment Hispanic 0 0 0 0 0 0 38
41 2022-01-01 Regulatory and Equipment Native American 0 0 0 0 0 0 2
42 2022-01-01 Regulatory and Equipment Other 0 0 0 0 0 0 13
43 2022-01-01 Regulatory and Equipment White 0 0 0 0 0 0 124
44 2022-01-01 Safety Violation Asian 0 0 0 0 0 0 59
45 2022-01-01 Safety Violation Black 2 0 1 1 0 6 1053
46 2022-01-01 Safety Violation Hispanic 1 1 1 1 0 1 303
47 2022-01-01 Safety Violation Native American 0 0 0 0 0 0 5
48 2022-01-01 Safety Violation Other 0 0 0 0 0 1 74
49 2022-01-01 Safety Violation White 0 0 0 0 0 0 636
50 NaT NaN NaN 17 7 6 2 0 38 17113

Breakdown of contraband seizures by race and stop purpose grouping since 2020.

Save Mecklenburg County Sheriff's Office Data to CSV¶

In [13]:
df = pd.read_sql(
    f"""
    WITH stops AS ({mecklenburg_driver_stops})
    SELECT
        *
    FROM stops
    """,
    pg_conn,
)
df.to_csv("mecklenburg-sheriff-2022-07.csv", index=False)